﻿'
' Will Strohl (wills@strohlsitedesign.com)
' http://www.willstrohl.com
'
'Copyright (c) 2010, Will Strohl
'All rights reserved.
'
'Redistribution and use in source and binary forms, with or without modification, are 
'permitted provided that the following conditions are met:
'
'Redistributions of source code must retain the above copyright notice, this list of 
'conditions and the following disclaimer.
'
'Redistributions in binary form must reproduce the above copyright notice, this list 
'of conditions and the following disclaimer in the documentation and/or other 
'materials provided with the distribution.
'
'Neither the name of StrohlSiteDesign.com nor the names of its contributors may be 
'used to endorse or promote products derived from this software without specific prior 
'written permission.
'
'THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY 
'EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 
'OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT 
'SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
'INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED 
'TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR 
'BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
'CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 
'ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH 
'DAMAGE.
'

Imports System.Text
Imports System.Text.RegularExpressions
Imports System.Web

Namespace WillStrohl.Modules.QueryToGrid

    Public Class QueryHandler
        Implements System.Web.IHttpHandler

#Region " Properties "

        Private p_Query As String = Null.NullString
        Private ReadOnly Property Query() As String
            Get
                Return p_Query
            End Get
        End Property

        Private p_Format As String = Null.NullString
        Private ReadOnly Property Format() As String
            Get
                Return Me.p_Format
            End Get
        End Property

        Private p_IncludeBody As Boolean = Null.NullBoolean
        Private ReadOnly Property IncludeBody() As Boolean
            Get
                Return Me.p_IncludeBody
            End Get
        End Property

#End Region

        Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

            If Not context.Request.QueryString("q") Is Nothing Then
                Me.p_Query = context.Request.QueryString("q").ToString
            End If

            If String.IsNullOrEmpty(Me.Query) Then
                context.Response.Write("")
                context.Response.End()
            End If

            If Not context.Request.QueryString("f") Is Nothing Then
                Me.p_Format = context.Request.QueryString("f").ToString.ToLower
            End If

            Select Case Me.Format
                Case "json"
                    context.Response.ContentType = "text/json"
                Case Else
                    context.Response.ContentType = "text/html"
            End Select

            If Not context.Request.QueryString("ib") Is Nothing Then
                If Regex.IsMatch(context.Request.QueryString("ib").ToLower, "^(true|false)$", RegexOptions.IgnoreCase) Then
                    Me.p_IncludeBody = Boolean.Parse(context.Request.QueryString("ib").ToString)
                End If
            End If

            context.Response.Write(Me.GetQueryResults)

        End Sub

        Private Function GetQueryResults() As String

            Select Case Me.Format
                Case "json"
                    Return Me.GetJSON
                Case Else
                    Return Me.GetTable
            End Select

        End Function

        Private Function GetTable() As String

            Dim sb As New StringBuilder(50)

            sb.Append("<table id=""tblGrid"" cellpadding=""0"" cellspacing=""0"" class=""display"">")

            Try

                Dim dt As DataTable = Nothing
                Dim dr As IDataReader = DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(Me.Query)

                If Not dr Is Nothing Then
                    dt = New DataTable
                    dt.Load(dr)

                    Dim columns As Integer = dt.Columns.Count
                    Dim rows As Integer = dt.Rows.Count


                    sb.Append("<thead><tr>")
                    For Each col As DataColumn In dt.Columns
                        sb.AppendFormat("<th>{0}</th>", col.ColumnName)
                    Next
                    sb.Append("</tr></thead><tbody>")

                    If Me.IncludeBody Then

                        For Each row As DataRow In dt.Rows
                            sb.Append("<tr>")
                            For intI As Integer = 0 To columns - 1
                                sb.AppendFormat("<td>{0}</td>", row(intI))
                            Next
                            sb.Append("</tr>")
                        Next

                    End If

                    sb.Append("</tbody><tfoot><tr>")
                    For Each col As DataColumn In dt.Columns
                        sb.AppendFormat("<th>{0}</th>", col.ColumnName)
                    Next
                    sb.Append("</tr></tfoot>")

                Else
                    sb.Append("<tr><td>No records returned from the query<td></tr>")
                End If

            Catch ex As Exception
                sb.AppendFormat("<tbody><tr><td>{0}<td></tr></tbody>", ex.Message)
            End Try

            sb.Append("</table>")

            Return sb.ToString

        End Function

        '
        ' return data that looks like this:
        '
        '{ "aaData": [
        '	["Trident","Internet Explorer 4.0","Win 95+","4","X"],
        '	["Trident","Internet Explorer 5.0","Win 95+","5","C"],
        '	["Trident","Internet Explorer 5.5","Win 95+","5.5","A"],
        '	["Other browsers","All others","-","-","U"]
        '] }
        '

        Private Function GetJSON() As String

            Dim sb As New StringBuilder(50)

            sb.Append("{""aaData"":[")

            Try

                Dim dt As DataTable = Nothing
                Dim dr As IDataReader = DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(Me.Query)

                If Not dr Is Nothing Then
                    dt = New DataTable
                    dt.Load(dr)

                    Dim columns As Integer = dt.Columns.Count
                    Dim rows As Integer = dt.Rows.Count
                    Dim intCount As Integer = 0

                    For Each row As DataRow In dt.Rows

                        sb.Append("[")

                        For intI As Integer = 0 To columns - 1

                            If intI = columns - 1 Then
                                sb.AppendFormat("""{0}""", row(intI))
                            Else
                                sb.AppendFormat("""{0}"",", row(intI))
                            End If

                        Next

                        If intCount < rows - 1 Then
                            sb.Append("],")
                        Else
                            sb.Append("]")
                        End If

                        intCount = intCount + 1

                    Next

                Else
                    sb.Append("[""No records returned from the query""]")
                End If

            Catch ex As Exception
                sb.AppendFormat("[""{0}""]", ex.Message)
            End Try

            sb.Append("]}")

            Return sb.ToString

        End Function

        ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
            Get
                Return False
            End Get
        End Property

    End Class

End Namespace